View in SQL – Complete Guide

A View in SQL is a virtual table created from a SQL query. Unlike a real table, a view does not store data physically inside the database. Instead, it stores a SELECT statement and shows the result whenever the view is used. Because of this, a view is also called a saved query. Views are useful when we want to simplify complex queries, restrict access to certain columns, or reuse the same query multiple times.

What is a View?

A view is created using the CREATE VIEW statement. When we select data from a view, SQL runs the query stored inside the view and returns the result.

Example:

CREATE VIEW EmployeeView
AS
SELECT Id, Name, Salary
FROM Employees;

Now we can use the view like a table:

SELECT * FROM EmployeeView;

The data shown in the view always comes from the original table, so if the table data changes, the view result also changes.

Why we use View

Views are used for many reasons in real projects:

To hide sensitive columns like salary or password
To simplify complex queries
To reuse the same query again and again
To provide security to users
To show data from multiple tables in one place

Example of hiding salary:

CREATE VIEW EmployeePublicView
AS
SELECT Id, Name
FROM Employees;

Users can see only Id and Name, not Salary.

View with Condition

We can create a view with a WHERE condition.

CREATE VIEW HighSalaryEmployees
AS
SELECT *
FROM Employees
WHERE Salary > 50000;

This view will show only employees with high salary.

View with Join

A view can also be created using multiple tables.

CREATE VIEW EmployeeDepartmentView
AS
SELECT e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d
ON e.DepartmentId = d.Id;

This type of view is called a complex view.

Types of View

There are two main types of views.

Simple View

Uses only one table
No group by
No join
Can insert/update sometimes

Complex View

Uses multiple tables
Uses join, group by, or functions
Cannot update in most cases

Example simple view:

CREATE VIEW V1 AS
SELECT * FROM Employees;

Example complex view:

CREATE VIEW V2 AS
SELECT DepartmentId, COUNT(*) Total
FROM Employees
GROUP BY DepartmentId;

Update View

We can change a view using ALTER.

ALTER VIEW EmployeeView
AS
SELECT Id, Name
FROM Employees;

Delete View

DROP VIEW EmployeeView;

Can we insert or update in View?

Yes, but only when the view is simple.

Allowed when:

One table
No group by
No distinct
No aggregate function
No join (in most cases)

Example:

INSERT INTO EmployeeView (Name, Salary)
VALUES ('Amit', 40000);

When to use View

Use view when:

Need reusable query
Need security
Need simple data access
Need to hide columns
Need join result

Conclusion

Views are very important in SQL because they make queries reusable, improve security, and simplify database access. In real-world applications, views are often used in reporting, dashboards, and user-level data access. Understanding views is important for SQL interviews and real projects.

interview